package main.com.oaer.common;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.type.LongType;
import org.hibernate.type.Type;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Component;

import main.com.oaer.model.PageList;

/**
 * 采用hibernate实现数据库操作
 * @author Administrator
 *
 */
@SuppressWarnings({"unchecked", "rawtypes"})
@Component
public class HibernateDaoUtil extends HibernateDaoSupport{
  
	/**
   * 注入数据源   
   */
	@Resource(name = "sessionFactory")
	public void setSuperSessionFactory(SessionFactory sessionFactory){
		super.setSessionFactory(sessionFactory);
	}
	
	/**
	 * 增加一个值对象
	 * @param entity 待插入对象
	 * @return 已执行插入操作的值对象的主键值
	 */
	public Serializable save(Object entity){
		return (Serializable) this.getHibernateTemplate().save(entity);
	}
	
	/**
	 * 增加或者修改一个对象
	 * @param entity  待插入对象
	 */
	public void saveOrUpdate(Object entity){
		this.getHibernateTemplate().saveOrUpdate(entity);
	}
	
	/**
	 * 批量增加一个集合数据
	 * @param entities 集合
	 */
	public void saveOrUpdateAll(Collection entities){
		this.getHibernateTemplate().saveOrUpdateAll(entities);
	}
	
	/**
	 * 批量保存对象
	 * @param entities  待插入对象集合
	 */
	public void saveAll(Collection entities){
		for(Object object:entities){
			this.getHibernateTemplate().save(object);
		}
	}
	
	/**
	 * 删除一个值对象
	 * @param entity 值对象
	 */
	public void delete(Object entity){
		this.getHibernateTemplate().delete(entity);
	}
	
	/**
	 * 根据主键数组删除一组对象
	 * @param c
	 * @param idArray
	 */
	public void delete(Class c,Collection<Long> idArray){
		for(Long id:idArray){
			this.getHibernateTemplate().delete(this.getHibernateTemplate().load(c, id));	
		}
	}
	
	/**
	 * 根据id列表批量删除一组对象
	 * @param c
	 * @param ids
	 */
	public void delete(Class c, List<Serializable> ids){
		for(Serializable id:ids){
			this.getHibernateTemplate().delete(this.getHibernateTemplate().get(c, id));
		}
	}
	
	/**
	 * 批量删除一组值对象
	 * @param collection
	 */
	public void delete (Collection collection){
		this.getHibernateTemplate().deleteAll(collection);
	}
	
	/**
	 * 更新一个值对象，如果值对象在数据库中不存在，则执行插入操作
	 * @param entity
	 */
	public void update(Object entity){
		this.getHibernateTemplate().update(entity);
	}
	
	/**
	 * 批量更新一组值对象
	 * @param collection
	 */
	public void update(Collection collection){
		this.getHibernateTemplate().saveOrUpdateAll(collection);
	}
	
	/**
	 * 批量更新一组值对象，当hibernate缓存存在两份一样的某个对象时
	 * @param collecttion
	 */
	public void updateAll(Collection collecttion){
		for(Object object:collecttion){
			this.getHibernateTemplate().merge(object);
		}
	}
	
	/**
	 * 当hibernate缓存存在两份一样的，必须用merge方法修改操作
	 * @param entity
	 */
	public void merge(Object entity){
		this.getHibernateTemplate().merge(entity);
	}
	
	/**
	 * 通过主键标识get方法查找某个对象
	 * @param <T>
	 * @param c
	 * @param id
	 * @return
	 */
	public <T> T get(Class<T> c, Serializable id){
		return this.getHibernateTemplate().get(c, id);
	}
	
	/**
	 * 通过主键标识load方式查找某个对象
	 * @param <T>
	 * @param c
	 * @param id
	 * @return
	 */
	public <T> T load(Class<T> c, Serializable id){
		return this.getHibernateTemplate().load(c, id);
	}
	
	/**
	 * 根据hql查询语句，动态加载参数
	 * @param hql
	 * @param o
	 * @return
	 */
	public List findListByHQL(String hql, Object... o){
		List list= new ArrayList();
		if(o!=null){
			list = this.getHibernateTemplate().find(hql, o);
		}else{
			list = this.getHibernateTemplate().find(hql);
		}
		return list;
	}
	
	/**
	 * 按游标起始值查询列表
	 * @param hql
	 * @param start  第几条记录开始查询
	 * @param pageSize  获取记录数量
	 * @param o  可变参数
	 * @return
	 */
	public List findListByHQL(final String hql, final Integer start, final Integer pageSize, final Object...o ){
		return this.getHibernateTemplate().execute(new HibernateCallback(){
			    
				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					Query query = session.createQuery(hql);
					if(o!=null){
						for(int i=0; i<o.length; i++){
							query.setParameter(i, o[i]);
						}
					}
					query.setFirstResult(start);
					query.setMaxResults(pageSize);
					return query.list();
				}
		});
	}
	
	/**
	 * 根据hql查询回map格式的结构
	 * @param hql
	 * @param size  如果传过来不是0或者不是null则代表需要按条数查询
	 * @param o
	 * @return
	 */
	public List<Map<String, Object>> findListMapByHQL(final String hql, final Integer size, final Object... o){
	     return this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
			    //设置返回map格式
				query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
				if(o!=null){
					for(int i=0; i<o.length; i++){
						query.setParameter(i, o[i]);
					}
				}
				if(size!=null && size!=0){
					query.setFirstResult(0);
					query.setMaxResults(size);
				}
				return query.list();
			}
	    	 
	     });
	}
	
	/**
	 * 通过sql语句查询列表
	 * @param sql 查询语句
	 * @param size  如果null或者0则查询所有
	 * @param scalarMap 如果用到统计函数的别名，则需要增加类型指定；否则置为null
	 * @param entityMap 如果需要转换回model实体，则需要增加类指定，；否则置为null
	 * @param o  参数
	 * @return
	 */
	public List<Map<String,Object>> findListMapBySql(final String sql, final Integer size, final Map<String, Type> scalarMap,
			final Map<String, Class<?>> entityMap, final Object... o){
		return this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery sqlQuery = session.createSQLQuery(sql);
				//设置返回MAP格式
				sqlQuery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
				//填充sql语句参数
				if(o!=null){
					for(int i=0; i<o.length; i++){
						sqlQuery.setParameter(i, o[i]);
					}
				}
				//用到统计函数需要对别名指定类型
				if(scalarMap!=null){
					for(String key:scalarMap.keySet()){
						sqlQuery.addScalar(key, scalarMap.get(key));
					}
				}
				//判断是否指定查询数量
				if(size!=null && size!=0){
					sqlQuery.setFirstResult(0);
					sqlQuery.setMaxResults(size);
				}
				//是否需要转换回model实体
				if(entityMap!=null){
					for(String key:entityMap.keySet()){
						sqlQuery.addEntity(key, entityMap.get(key));
					}
				}
				return sqlQuery.list();
			}
			
		});
	}
	
	/**
	 * 根据hql查询回Map类型的单个对象
	 * @param hql
	 * @param o
	 * @return
	 */
	public Map<String, Object> findEntityMapByHQL(final String hql, final Object... o){
		return this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Map<String,Object> doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				//设置返回map格式
				query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
				if(o!=null){
					for(int i=0; i<o.length; i++){
						query.setParameter(i, o[i]);
					}
				}
				return  (Map<String, Object>)query.uniqueResult();				
			}			
		});
	}
	
	/**
	 * 通过sql语句查询返回唯一Map结果集
	 * @param sql
	 * @param scalarMap
	 * @param entityMap
	 * @param param
	 * @return
	 */
	public Map<String, Object> findEntityMapBySQL(final String sql, final Map<String, Type> scalarMap,
			final Map<String,Class<?>> entityMap, final Object...param){
		return this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public  Map<String, Object> doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery sqlQuery = session.createSQLQuery(sql);
				//设置返回MAP格式
				sqlQuery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
				//填充sql语句参数
				if(param!=null){
					for(int i=0; i<param.length; i++){
						sqlQuery.setParameter(i, param[i]);
					}
				}
				//用到统计函数需要对别名指定类型
				if(scalarMap!=null){
					for(String key:scalarMap.keySet()){
						sqlQuery.addScalar(key, scalarMap.get(key));
					}
				}
				//是否需要转换回model实体
				if(entityMap!=null){
					for(String key:entityMap.keySet()){
						sqlQuery.addEntity(key, entityMap.get(key));
					}
				}
				return ( Map<String, Object>)sqlQuery.uniqueResult();
			}
			
		});
	}
	
	/**
	 * 根据hql查询语句，并动态加载参数
	 * @param hql 查询语句
	 * @param o  不需要参数时置为0或者null
	 * @return  model
	 */
	public Object findEntityByHQL(final String hql, Object... o){
		List list = new ArrayList();
		if(o!=null){
			list = this.getHibernateTemplate().find(hql, o);
		}else{
			list = this.getHibernateTemplate().find(hql);
		}
		for(Object object: list){
			return object;
		}
		return null;
	}
	
	/**
	 * 通过hql分页操作，需自己传入查询总数的hql
	 * @param hql hql查询list结果集语句
	 * @param countHQL hql查询总数
	 * @param currentPage 当前页
	 * @param pageSize 每页显示数量
	 * @param o 可变参数
	 * @return
	 */
	public PageList findListPageByHQL(final String hql, final String countHQL,
			final int currentPage, final int pageSize, final Object...o){
		return (PageList)this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				int firstRow = (currentPage - 1) * pageSize;
				Query query = session.createQuery(hql);
				Query countQuery= session.createQuery(countHQL);
				if(o!=null){
					for(int i=0; i<o.length; i++){
						query.setParameter(i, o[i]);
						countQuery.setParameter(i, o[i]);
					}
				}
				query.setFirstResult(firstRow);
				query.setMaxResults(pageSize);
				List list = new ArrayList();
				//查询总记录数
				Long totalSize = (Long)countQuery.uniqueResult();
				//判断是当前页是否超出总页数
				int countPage = (int)(totalSize/pageSize);
				if(totalSize % pageSize != 0){
					countPage++;
				}
				if(currentPage > countPage){
					PageList pageList = new PageList(countPage, totalSize, pageSize);
					pageList.setList(list);
					return pageList;
				}
				list = query.list();
				PageList pageList = new PageList(currentPage, totalSize, pageSize);
				pageList.setList(list);
				return pageList;
			}
			
		});
	}
	
	/**
	 * 通过hql分页操作
	 * @param hql  hql 查询list结果集语句
	 * @param currentPage  当前页
	 * @param pageSize 每页显示数量
	 * @param o 可变参数
	 * @return
	 */
	public PageList findListPageByHQL(final String hql, final int currentPage, final int pageSize, 
			final Object... o){
		return (PageList)this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				int firstRow = (currentPage - 1) * pageSize;
				Query query = session.createQuery(hql);
				String countHQL = " select count(*) from "+hql.replaceFirst(".*?FROM","").replaceFirst(".*?from", "");
				Query countQuery = session.createQuery(countHQL);
				if(o!=null){
					for(int i = 0; i< o.length; i++){
						query.setParameter(i, o[i]);
						countQuery.setParameter(i, o[i]);
					}
				}
				query.setFirstResult(firstRow);
				query.setMaxResults(pageSize);
				List list = new ArrayList();
				//查询总记录数
				Long totalSize = (Long) countQuery.uniqueResult();
				//判断是当前页是否超出总页数
				int countPage = (int)(totalSize/pageSize);
				if(totalSize % pageSize !=0){
					countPage++;
				}
				if(currentPage > countPage){
					PageList pageList = new PageList(countPage, totalSize, pageSize);
					pageList.setList(list);
					return pageList;
				}
				list = query.list();
				PageList pageList = new PageList(currentPage, totalSize, pageSize);
				pageList.setList(list);
				return pageList;
			}
			
		});
	}
	
	/**
	 * 通过hql分页操作，结果集为List<Map>形式，并需自己传入查询总数的hql
	 * @param hql
	 * @param countHql
	 * @param currentPage
	 * @param pageSize
	 * @param o
	 * @return
	 */
	public PageList findListMapPageByHQL(final String hql, final String countHql,
			final int currentPage, final int pageSize, final Object... o){
		return this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				Query countQuery = session.createQuery(countHql);
				//返回结果map方式
				query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
				//hql 参数赋值
				if(o!=null){
					for(int i=0; i<o.length; i++){
						query.setParameter(i, o[i]);
						countQuery.setParameter(i, o[i]);
					}
				}
				//查询总记录数
				Long totalSize = (Long)countQuery.uniqueResult();
				//判断是当前页是否超出总页数
				int countPage = (int) (totalSize/pageSize);
				if(totalSize % pageSize !=0){
					countPage++;
				}
				//计算查询游标开始和结束位置
				int firstRow = 0;
				//如果传过来的页码超过总页数，则按照最后一页处理
				if(currentPage > countPage){
					firstRow = (countPage - 1)*pageSize;
				}else{
					firstRow = (currentPage - 1)*pageSize;
				}
				query.setFirstResult(firstRow);
				query.setMaxResults(pageSize);
				List list = query.list();
				PageList pageList = new PageList(currentPage, totalSize, pageSize);
				pageList.setList(list);
				return pageList;
			}
			
		});
	}
	
	/**
	 * 通过hql分页操作，结果集为List<Map>形式
	 * @param hql
	 * @param currentPage
	 * @param pageSize
	 * @param o
	 * @return
	 */
	public PageList findListMapPageByHQL(final String hql, final int currentPage, final int pageSize, final Object... o){
		return this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				String countHql = "select count(*) from "+hql.replaceFirst(".*?FROM", "").replace(".*?from", "");
				Query countQuery = session.createQuery(countHql);
				//返回结果为map方式
				query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
				//hql 参数赋值
				if(o!=null){
					for(int i=0; i<o.length; i++){
						query.setParameter(i, o[i]);
						countQuery.setParameter(i, o[i]);
					}
				}
				//查询总记录数
				Long totalSize = (Long)countQuery.uniqueResult();
				//判断是当前页是否超出总页数
				int countPage = (int) (totalSize/pageSize);
				if(totalSize % pageSize !=0){
					countPage++;
				}
				//计算查询游标开始和结束位置
				int firstRow = 0;
				//如果传过来的页码超过总页数，则按照最后一页处理
				if(currentPage > countPage){
					firstRow = (countPage - 1)*pageSize;
				}else{
					firstRow = (currentPage - 1)*pageSize;
				}
				query.setFirstResult(firstRow);
				query.setMaxResults(pageSize);
				List list = query.list();
				PageList pageList = new PageList(currentPage, totalSize, pageSize);
				pageList.setList(list);
				return pageList;
			}
			
		});	
	}
	
	/**
	 * 通过sql分页操作，需自己传入查询总数的sql
	 * @param sql sql查询list结果集语句
	 * @param countSQL sql查询总数
	 * @param currentPage 当前页
	 * @param pageSize 每页显示数量
	 * @param o 可变参数
	 * @return
	 */
	public PageList findListPageBySQL(final String sql, final String countSQL,
			final int currentPage, final int pageSize, final Object...o){
		return this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery sqlQuery = session.createSQLQuery(sql);
				Query countQuery= session.createSQLQuery(countSQL);
				if(o!=null){
					for(int i=0; i<o.length; i++){
						sqlQuery.setParameter(i, o[i]);
						countQuery.setParameter(i, o[i]);
					}
				}
				//查询总记录数
				Long totalSize = (Long)countQuery.uniqueResult();
				//判断是当前页是否超出总页数
				int countPage = (int)(totalSize/pageSize);
				if(totalSize % pageSize != 0){
					countPage++;
				}
				//计算查询游标开始和结束位置
				int firstRow = 0;
				//如果传过来的页码超过总页数，则按照最后一页处理
				if(currentPage > countPage){
					firstRow = (countPage - 1)*pageSize;
				}else{
					firstRow = (currentPage - 1)*pageSize;
				}
				sqlQuery.setFirstResult(firstRow);
				sqlQuery.setMaxResults(pageSize);
				List list = sqlQuery.list();
				PageList pageList = new PageList(currentPage, totalSize, pageSize);
				pageList.setList(list);
				return pageList;
			}
			
		});
	}
	
	/**
	 * 通过 sql 语句查询List<Map> 分页列表
	 * @param sql  查询语句
	 * @param currentPage  当前页码
	 * @param pageSize  每页显示数量
	 * @param scalarMap 如果用到统计函数的别名，则需要增加类型指定；否则置为null
	 * @param entityMap 如果需要转换回model实体，则需要增加类指定，；否则置为null
	 * @param o  可变参数
	 * @return
	 */
	public PageList findListMapPageBySQL(
			final String sql, 
			final int currentPage, 
			final int pageSize, 
			final Map<String,Type> scalarMap, 
			final Map<String,Class<?>> entityMap, 
			final Object... o){

		return this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery sqlQuery = session.createSQLQuery(sql);
				String countSql = "select count(*) from "+sql.replaceFirst(".*?FROM", "").replace(".*?from", "");
				SQLQuery countQuery = session.createSQLQuery(countSql);
				countQuery.addScalar("count(*)", LongType.INSTANCE);
				//返回结果为map方式
				sqlQuery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
				//hql 参数赋值
				if(o!=null){
					for(int i=0; i<o.length; i++){
						sqlQuery.setParameter(i, o[i]);
						countQuery.setParameter(i, o[i]);
					}
				}
				//如果用到统计函数需要对别名指定类型
				if(scalarMap != null){
					for(String key:scalarMap.keySet()){
						sqlQuery.addScalar(key, scalarMap.get(key));
					}
				}
				//是否需要转换回model实体				
				if(entityMap!=null){
					for(String key:entityMap.keySet()){
						sqlQuery.addEntity(key, entityMap.get(key));
					}
				}
				//查询总记录数
				Long totalSize = (Long)countQuery.uniqueResult();
				//判断是当前页是否超出总页数
				int countPage = (int) (totalSize/pageSize);
				if(totalSize % pageSize !=0){
					countPage++;
				}
				//计算查询游标开始和结束位置
				int firstRow = 0;
				//如果传过来的页码超过总页数，则按照最后一页处理
				if(currentPage > countPage){
					firstRow = (countPage - 1)*pageSize;
				}else{
					firstRow = (currentPage - 1)*pageSize;
				}
				sqlQuery.setFirstResult(firstRow);
				sqlQuery.setMaxResults(pageSize);
				List list = sqlQuery.list();
				PageList pageList = new PageList(currentPage, totalSize, pageSize);
				pageList.setList(list);
				return pageList;
			}
			
		});	
	}
	
	/**
	 * 执行操作
	 */
	public void flush(){
		this.getSession().flush();
	}
	
	/**
	 * 执行sql的增删改，注意此方法批量时会导致缓存不同步
	 * @param sql
	 * @param o
	 * @return
	 */
	public int executeUpdateBySQL(final String sql, final Object... o){
		return this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery sqlQuery = session.createSQLQuery(sql);
				if(o!=null){
					for(int i=0; i<o.length; i++){
						sqlQuery.setParameter(i, o[i]);
					}
				}
				return sqlQuery.executeUpdate();
			}
			
		});
	} 
	
	/**
	 * 执行hql的增删改，注意此方法批量时会导致缓存不同步
	 * @param hql
	 * @param o
	 * @return
	 */
	public int executeUpdateByHQL(final String hql, final Object... o ){
		return this.getHibernateTemplate().executeWithNewSession(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				if(o!=null){
					for(int i=0; i<o.length; i++){
						query.setParameter(i, o[i]);
					}
				}
				return query.executeUpdate();
			}
			
		});
	}
	
	/**
	 * 根据条件获取记录条数
	 * @param hql
	 * @return
	 */
	 public  Integer getRows(final String hql, final Object... o){
			return this.getHibernateTemplate().executeWithNewSession(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					Query query = session.createQuery(hql);
					if(o!=null){
						for(int i=0; i<o.length; i++){
							query.setParameter(i, o[i]);
						}
					}
					String rowNumber = query.uniqueResult().toString();
					return new Integer(rowNumber);
				}
				
			});
	 }
	 
	   /**
		 * 根据条件获取某个字段的唯一值
		 * @param hql
		 * @return
		 */
		 public  String getOneRowValue(final String hql, final Object... o){
				return this.getHibernateTemplate().executeWithNewSession(new HibernateCallback(){

					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Query query = session.createQuery(hql);
						if(o!=null){
							for(int i=0; i<o.length; i++){
								query.setParameter(i, o[i]);
							}
						}
						return query.uniqueResult().toString();
					}
					
				});
		 }
}
